package java6699.dao.gui;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Function;

public class Student6699Dao {

    public int add6699(Student6699 student){
        //连接数据库并执行SQL语句
        String sql = "INSERT INTO xslist(sno,sname,lx1,lx2) VALUES (?,?,?,?)";
        String[] values={student.getSno(),student.getSname(),
                student.getLx1()+"",student.getLx2()+""};
        int id=excute6699(sql,values,st->{
            try(ResultSet rs=st.getGeneratedKeys()) {
                if (rs.next()){
                    return rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return -1;
        });
        return id;
    }
    private int excute6699(String strSql, String[] values, Function<PreparedStatement,Integer> getId){
        Connection conn=null;
        PreparedStatement preSt=null;
        int result=-1;
        try{
            conn= DBUtil6699.getConnection6699();
            preSt=conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
            for (int i=0;i<values.length;i++){
                preSt.setString(i+1,values[i]);
            }
            result = preSt.executeUpdate();
            if (getId !=null && result ==1){
                result=getId.apply(preSt);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil6699.close(preSt,conn);
        }
        return result;
    }

    public int update6699(int id, Student6699 newStudent){
        //连接数据库并执行SQL语句
        String sql = "UPDATE xslist SET sno=?,sname=?,lx1=?,lx2=? WHERE id = ?";
        String[] values={newStudent.getSno(),newStudent.getSname()
                ,newStudent.getLx1()+"",newStudent.getLx2()+"",id+""};
        return excute6699(sql,values,null);
    }

    public int delete6699(int id){
        //连接数据库并执行SQL语句
        String sql = "DELETE from xslist where id=?";
        String[] values={id+""};
        return excute6699(sql,values,null);
    }

    public List<Student6699> findAll6699(){
        String sql="select * from xslist";
        String[] values ={};
        List<Student6699> stulist = find6699(sql,values);
        return stulist;
    }

    public Student6699 findStudent6699(String sno){
        String sql="select * from xslist where sno=?";
        String[] values={sno};
        List<Student6699> stulist=find6699(sql,values);
        if (stulist !=null && stulist.size()>0){
            return stulist.get(0);
        }else {
            return null;
        }
    }
    private List<Student6699> find6699(String strSql, String[] values ){
        List<Student6699> stulist=null;
        Connection conn=null;
        PreparedStatement preSt=null;
        ResultSet rs=null;
        try{
            conn= DBUtil6699.getConnection6699();
            preSt=conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
            for (int i=0;i<values.length;i++){
                preSt.setString(i+1,values[i]);
            }
            rs= preSt.executeQuery();
            stulist=new ArrayList<>();
            while (rs.next()){
                Student6699 student=new Student6699();
                student.setId(rs.getInt("id"));
                student.setSno(rs.getString("sno"));
                student.setSname(rs.getString("sname"));
                student.setLx1(rs.getInt("lx1"));
                student.setLx2(rs.getInt("lx2"));
                stulist.add(student);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil6699.close(rs,preSt,conn);
        }
        return stulist;
    }
}
